/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.jgls.bi.dataprocess;

import java.sql.PreparedStatement;
import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Iterator;
import java.util.ArrayList;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

/**
 *
 * @author ben
 */
public class biSaleYybHzJdbcDao extends JdbcDaoSupport implements biSaleYybHzDataDao {

    @Override
    public void biSaleYybHzData(Date adTsnDate, int aiShopId, String asShopBm, int aiDateNo, String asYear, int aiYybh, int aiMan_id, String asTname) {
        int iRc = 0;
        int aiYear = 0;
        int iSup_id = 0;
        int iYyb = 0;
        String sql = "";
        float fValue = 0, fAmt = 0;
        List lstSale;
        String sLb = "";
        String sMc = "";
        final int isize;
        final Date dd;
        ArrayList<String> alLb = new ArrayList<String>();
        ArrayList<String> alMc = new ArrayList<String>();
        ArrayList<Integer> alYyb = new ArrayList<Integer>();
        ArrayList<Integer> alSup = new ArrayList<Integer>();
        ArrayList<Float> alValue = new ArrayList<Float>();
        ArrayList<Float> alAmt = new ArrayList<Float>();
        final ArrayList<String> alLb2 = new ArrayList<String>();
        final ArrayList<String> alMc2 = new ArrayList<String>();
        final ArrayList<Integer> alYyb2 = new ArrayList<Integer>();
        final ArrayList<Integer> alSup2 = new ArrayList<Integer>();
        final ArrayList<Float> alValue2 = new ArrayList<Float>();
        final ArrayList<Float> alAmt2 = new ArrayList<Float>();

        //将数据库设置为固定的t_spsjk该表运行于168.42.8.10机器上，为分区表
        SimpleDateFormat ddd = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Calendar rightNow = Calendar.getInstance();
        rightNow.add(Calendar.DATE, -2);
        String lsYesterday = ddd.format(rightNow.getTime());
        java.sql.Date ldYesterday = java.sql.Date.valueOf(lsYesterday);
        //该程序只计算前天的数据，因程序运行时昨天的数据还未生成
        adTsnDate = ldYesterday;
        //该程序只运行一次即可，所以目前定为2店，如2店闭店，请换为其他门店
        if (aiShopId != 2) {
            return;
        }

        if (adTsnDate.before(ldYesterday)) {
            //汇总表
            asTname = "t_plumx@dvlpr19 ";
        } else {
            //工作表，只存储昨天和前天的数据
            asTname = "t_plumx@dvlpr19 ";
        //sTname = " t_waste_book ";
        }
        System.out.println("now processing  " + asTname + "  biSaleYybHz's Data... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        aiYear = Integer.parseInt(asYear);
        //取总库存金额
        JdbcTemplate jt = getJdbcTemplate();
        //begin insert into lotteryMx
        //1:delete
        Object[] params = new Object[]{adTsnDate};
        int[] types = new int[]{Types.DATE};
        sql = "delete from t_bi_sale_yyb_hz where tsn_date = ? ";
        iRc = jt.update(sql, params, types);
        //取数据
        sql = "select b.sup_id,a.lb,b.mc,c.yybh,sum(a.sl) sl,sum(a.je) je" +
                " from sy_plumx a,sy_spk b,sy_archi_dept c " +
                "where a.lb=b.lb and a.bm=c.bm and a.saledate = ? group by b.sup_id,a.lb,b.mc,c.yybh";
        params = new Object[]{adTsnDate};
        types = new int[]{Types.DATE};
        lstSale = jt.query(sql, params, types, new RowMapperResultReader(new SaleYybHz()));
        Iterator Iter = lstSale.iterator();
        biSaleYybHz ltam = new biSaleYybHz();
        while (Iter.hasNext()) {
            ltam = (biSaleYybHz) Iter.next();
            fValue = ltam.getSale_value();
            fAmt = ltam.getSale_amt();
            iYyb = ltam.getYybh();
            iSup_id = ltam.getSup_id();
            sLb = ltam.getLb().trim();
            sMc = ltam.getMc().trim();
            if ((iSup_id > 9999999) || (iSup_id == 0)) {
                continue;
            }
            if ((fValue == 0) || (fAmt == 0)) {
                continue;
            }
            if (iYyb == 0) {
                continue;
            }
            alValue.add(fValue);
            alAmt.add(fAmt);
            alYyb.add(iYyb);
            alSup.add(iSup_id);
            alLb.add(sLb);
            alMc.add(sMc);

            //2:insert
            /*
            sql = "insert into t_bi_sale_yyb_hz " +
                    "(tsn_date,yybh,sup_id,lb,mc,sale_value,sale_amt)" +
                    " values(?,?,?,?,?,?,?)";
            params = new Object[]{adTsnDate, iYyb, iSup_id, sLb, sMc, fValue, fAmt};
            types = new int[]{Types.DATE, Types.INTEGER,
                        Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.FLOAT, Types.FLOAT};
            iRc = jt.update(sql, params, types);
             */
        }
        isize = alLb.size();
        for (int i = 0; i < isize; i++) {
            alLb2.add(alLb.get(i));
            alMc2.add(alMc.get(i));
            alValue2.add(alValue.get(i));
            alAmt2.add(alAmt.get(i));
            alYyb2.add(alYyb.get(i));
            alSup2.add(alSup.get(i));
        }

        if (isize > 0) {
            dd = adTsnDate;
            //2:insert
            sql = "insert into t_bi_sale_yyb_hz " +
                    "(tsn_date,yybh,sup_id,lb,mc,sale_value,sale_amt)" +
                    " values(?,?,?,?,?,?,?)";
            BatchPreparedStatementSetter setter = null;
            setter = new BatchPreparedStatementSetter() {

                @Override
                public int getBatchSize() {
                    return isize;
                }

                @Override
                public void setValues(PreparedStatement ps, int index) throws SQLException {
                    ps.setDate(1, dd);
                    ps.setInt(2, alYyb2.get(index));
                    ps.setInt(3, alSup2.get(index));
                    ps.setString(4, alLb2.get(index));
                    ps.setString(5, alMc2.get(index));
                    ps.setFloat(6, alValue2.get(index));
                    ps.setFloat(7, alAmt2.get(index));
                }
            };
            jt.batchUpdate(sql, setter);
        }
    }

    class SaleYybHz implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            biSaleYybHz lmx = new biSaleYybHz();
            //lmx.setTotal_value(rs.getFloat("total_value"));
            lmx.setLb(rs.getString("lb"));
            lmx.setMc(rs.getString("mc"));
            lmx.setSale_amt(rs.getFloat("sl"));
            lmx.setSale_value(rs.getFloat("je"));
            lmx.setSup_id(rs.getInt("sup_id"));
            lmx.setYybh(rs.getInt("yybh"));
            return lmx;
        }
    }
}
